package com.householditemtwo.admin.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.householditemtwo.common.entity.Product;
import com.householditemtwo.common.utils.DBManager;
import com.householditemtwo.common.utils.QueryDataResult;

/**
 * 用户DAO
 * @author admin
 *
 */
	public class ProductDao {
			
		/**
		 * 查询所有产品的总条数(分页)
		 * @return
		 */
			public int count(String proname) {
				DBManager db=new DBManager();
				String sql="select count(*) from Product ";
				if (proname !=null && proname !="" ) {
					sql+="where proname like '%"+proname+"%'";
				}
				QueryDataResult qdr=db.executeQuery(sql);
			int total=0;
				try {
					while(qdr.rs.next()) {
					total=qdr.rs.getInt(1);
					}
				}catch(SQLException e) {
					e.printStackTrace();
				}
				//返回查询的结果
				return total;
			}
			
		/**
		 * 查询所有产品
		 * @return
		 */
			public List<Product> selectAllProduct( int page, int pageSize,String proname) {
				DBManager db=new DBManager();
				String sql="select * from Product ";
						
					if (proname !=null && proname !="" ) {
							sql  +=" where proname like '%"+proname+"%'";
					}
					sql+= " order by proid limit "+pageSize*(page-1)+","+pageSize;
				QueryDataResult qdr=db.executeQuery(sql);
				Product emp=new Product();
				ArrayList<Product> list=new ArrayList<Product>();
				try {
					while(qdr.rs.next()) {
						Product c=new Product();
						c.setProid(qdr.rs.getInt(1));
						c.setProname(qdr.rs.getString(2));
						c.setProprice(qdr.rs.getDouble(3));
						c.setProstock(qdr.rs.getInt(4));
						c.setPteid(qdr.rs.getInt(5));
						c.setProresult(qdr.rs.getString(6));
						c.setProdefauiimg(qdr.rs.getString(7));
						c.setProchoice(qdr.rs.getInt(8));
						c.setProplace(qdr.rs.getString(9));
						c.setPromaterial(qdr.rs.getString(10));
						c.setProunit(qdr.rs.getString(11));
						c.setProshell(qdr.rs.getString(12));
						c.setPropaint(qdr.rs.getString(13));
						
						list.add(c);
					}
				}catch(SQLException e) {
					e.printStackTrace();
				}
				//返回查询的结果
				return list;
			}
			
			
			/**
			 * 修改产品
			 * @return
			 */
		
		public int update(Product p) {
				//写sql
			String sql= String.format("update product set proname='%s',"
					+ "proPrice=%f,proDefauiImg='%s',proPlace='%s',proMaterial='%s',"
					+"proUnit='%s',proShell='%s',proStock=%d,proChoice=%d,pteid=%d where proId=%d",
					p.getProname(),p.getProprice(),p.getProdefauiimg(),p.getProplace(),
					p.getPromaterial(),p.getProunit(),p.getProshell(),p.getProstock(),p.getProchoice(),p.getPteid(),p.getProid());
					
				//执行sql
				return	DBManager.executeUpdate(sql);
			
			
		}
		/**
		 * 新增产品
		 * @return
		 */

		public int insert(Product p) {
			//拼接sql
			String sql= String.format("insert into product(proname,proPrice,proDefauiImg,proPlace, "
					+"proMaterial,proUnit,proShell,proStock,proChoice,pteid) value('%s',%f,'%s','%s','%s','%s','%s',%d,%d,%d) ",
					p.getProname(),p.getProprice(),p.getProdefauiimg(),p.getProplace(),
					p.getPromaterial(),p.getProunit(),p.getProshell(),p.getProstock(),p.getProchoice(),p.getPteid());
					System.out.println(sql);
			//执行sql
		return	DBManager.executeUpdate(sql);
			
			
		}
	
		/**
		 * 删除产品
		 * @return
		 */
			public int delete(int id) {
				//拼接sql
				String sql= "delete from product where proid="+id;
						
				//执行sql
			return	DBManager.executeUpdate(sql);
			}
	
}
